package fy.hr.reinstatement;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.json.JSONArray;
import org.json.JSONObject;

import fy.config.FyConfig;
import fy.hr.bean.JobBean;
import fy.hr.bean.OptionBean;
import fy.util.DBUtil;
import fy.util.MyUtil;

class ReinstatementDao {

	private static Log log = LogFactory.getLog(ReinstatementDao.class);

	private String queryMemberSQL = "select tab2.psncode,tab2.psnname,def1.docname,def2.docname,tab1.BIRTHDATE,tab1.basgroupdef4,tab2.OUTDUTYDATE,trunc(to_number(to_date(tab2.OUTDUTYDATE,'YYYY-MM-DD') - to_date(tab1.basgroupdef4,'YYYY-MM-DD')) / 365,2),c.unitname, c.pk_corp, d1.deptname, d1.pk_deptdoc, d2.deptname, d2.pk_deptdoc,j.jobname, j.pk_jobdoc, def3.docname from bd_psnbasdoc tab1 join bd_psndoc tab2 on tab1.pk_psnbasdoc=tab2.pk_psnbasdoc   join (select pk_psnbasdoc, max(leavedate) as leavedate from hi_psndoc_dimission group by pk_psnbasdoc) tab3 on tab2.pk_psnbasdoc = tab3.pk_psnbasdoc and tab2.OUTDUTYDATE = tab3.leavedate left join bd_defdoc def1 on tab1.NATIVEPLACE=def1.PK_DEFDOC left join bd_defdoc def2 on tab2.groupdef14=def2.pk_defdoc left join bd_corp c on tab2.pk_corp=c.pk_corp left join bd_deptdoc d1 on d1.pk_deptdoc = tab2.pk_deptdoc left join bd_deptdoc d2 on d1.pk_fathedept = d2.pk_deptdoc left join om_job j on tab2.pk_om_job = j.pk_om_job left join bd_defdoc def3 on j.jobrank = def3.pk_defdoc where tab2.psncode = ? and tab2.dr = 0";

	private String queryLvliSQL = "select * from (select * from (select w.begindate 起始时间,       w.enddate 结束时间,       replace(replace(replace(w.workcorp, ',', '，'), chr(10), ''),               chr(13),               '') 单位,       replace(replace(replace(w.workdept, ',', '，'), chr(10), ''),               chr(13),               '') 部门,       trim(decode(w.workjob,                   '',                   '',                   '  ' || replace(replace(replace(w.workjob, ',', '，'),                                           chr(10),                                           ''),                                   chr(13),                                   '')) ||             decode(w.workpost,                    '',                    '',                    '  ' || replace(replace(replace(w.workpost, ',', '，'),                                            chr(10),                                            ''),                                    chr(13),                                    ''))) 职务   from hi_psndoc_work w   left join bd_psndoc d on w.pk_psnbasdoc = d.pk_psnbasdoc  where     nvl(d.dr, 0) = 0   and d.psncode = ?   and nvl(w.dr, 0) = 0   and w.pk_deptchg is not null )   group by 起始时间,结束时间,单位,部门,职务) order by 起始时间 asc";

	private String queryNewDepInfo = "select c.unitname, d1.deptname, d1.pk_deptdoc, d2.deptname, d2.pk_deptdoc from bd_psndoc p left join bd_deptdoc d1 on p.pk_deptdoc = d1.pk_deptdoc left join bd_deptdoc d2 on d1.pk_fathedept = d2.pk_deptdoc left join bd_corp c on p.pk_corp = c.pk_corp where p.psncode = ? and p.psnclscope = '0'";

	private String queryNewPosts = "select j.jobname, j.pk_jobdoc, def.docname from om_job j left join bd_deptdoc d on j.pk_deptdoc = d.pk_deptdoc left join bd_defdoc def on j.jobrank = def.pk_defdoc where j.dr = '0' and (d.pk_deptdoc = ? or d.pk_fathedept = ?)";

	private String loginMemberCode;// 填单人工号

	private String memberCode;// 申请复职的人员工号

	private String errorMsg;

	private static final String test = "test11111111111111111";

	private ReinstatementInfoBean reinstatementInfoBean;

	public ReinstatementInfoBean getReinstatementInfoBean() {
		return reinstatementInfoBean;
	}

	public void setLoginMemberCode(String loginMemberCode) {
		this.loginMemberCode = loginMemberCode;
	}

	public void setMemberCode(String memberCode) {
		this.memberCode = memberCode;
	}

	public String getErrorMsg() {
		return errorMsg;
	}

	/**
	 * 所有制造部
	 * 
	 * @return
	 */
	public boolean query() {
		Connection conn = null;
		PreparedStatement pst = null;
		ResultSet rs = null;

		boolean success = false;
		// 人员是否已离职
		boolean leave = true;
		System.out.println(test);
		try {
			conn = DBUtil.connect(FyConfig.getEHR_DB_CONFIG());

			reinstatementInfoBean = new ReinstatementInfoBean();

			// 查询复职人员信息
			pst = conn.prepareStatement(queryMemberSQL);

			pst.setString(1, memberCode);

			rs = pst.executeQuery();

			if (rs.next()) {

				// 判断是否离职人员
				if (rs.getString(7) != null && !"".equals(rs.getString(7))) {
					reinstatementInfoBean.setMemberCode(rs.getString(1));
					reinstatementInfoBean.setMemberName(rs.getString(2));
					reinstatementInfoBean.setAddress(rs.getString(3));
					reinstatementInfoBean.setEducationalBackground(rs
							.getString(4));
					reinstatementInfoBean.setBirthdate(rs.getString(5));
					reinstatementInfoBean.setLastJoinDate(rs.getString(6));
					reinstatementInfoBean.setLastLeaveDate(rs.getString(7));
					reinstatementInfoBean.setWorkYear(String.valueOf(rs
							.getFloat(8)));

					reinstatementInfoBean.setOldCompany(new OptionBean(rs
							.getString(10), rs.getString(9)));

					// 判断是否有上级部门
					String pk_dept = rs.getString(14);
					if (pk_dept != null && !"".equals(pk_dept)) {
						reinstatementInfoBean.setOldChildDep((new OptionBean(rs
								.getString(12), rs.getString(11))));
						reinstatementInfoBean.setOldDep((new OptionBean(
								pk_dept, rs.getString(13))));
					} else {
						reinstatementInfoBean.setOldDep((new OptionBean(rs
								.getString(12), rs.getString(11))));
					}

					JobBean jobBean = new JobBean();
					jobBean.setId(rs.getString(16));
					jobBean.setName(rs.getString(15));
					jobBean.setRank(rs.getString(17));
					reinstatementInfoBean.setOldPost(jobBean);
					// 人员信息存在
					reinstatementInfoBean.setExist(true);

					DBUtil.close(rs, pst);

					pst = conn.prepareStatement(queryLvliSQL);

					pst.setString(1, memberCode);

					rs = pst.executeQuery();
					JSONArray ja = new JSONArray();
					while (rs.next()) {
						JSONObject jo = new JSONObject();
						jo.put("起始日期", rs.getString(1));
						jo.put("终止日期", rs.getString(2));
						jo.put("工作单位", rs.getString(3));
						jo.put("所在部门", rs.getString(4));
						jo.put("岗位", rs.getString(5));

						ja.put(jo);
					}
					reinstatementInfoBean.setLvliJSON(ja.toString());
					DBUtil.close(rs, pst);

				} else {
					errorMsg = "该用户没有离职，请确认复职人员工号。";
					leave = false;
				}
			} else {

				DBUtil.close(rs, pst);
				reinstatementInfoBean.setExist(false);

				/**
				 * 查询公司列表
				 */
				List<OptionBean> companyList = new ArrayList<OptionBean>();

				pst = conn
						.prepareStatement("select c1.pk_corp, c1.unitname from bd_corp c1 where c1.dr = 0 and not exists ( select * from bd_corp c2 where c1.pk_corp = c2.fathercorp and c1.dr = 0 and c2.dr = 0)");

				rs = pst.executeQuery();

				while (rs.next()) {
					OptionBean bean = new OptionBean();

					bean.setId(rs.getString(1));
					bean.setName(rs.getString(2));

					companyList.add(bean);
				}
				reinstatementInfoBean.setCompanyList(companyList);
				DBUtil.close(rs, pst);

				reinstatementInfoBean.setMemberCode(memberCode);
			}

			// 人员已经离职
			if (leave) {

				// 申请人所在制造部、部门
				pst = conn.prepareStatement(queryNewDepInfo);
				pst.setString(1, loginMemberCode);
				rs = pst.executeQuery();

				// select c.unitname, d1.deptname, d1.pk_deptdoc, d2.deptname,
				// d2.pk_deptdoc from bd_psndoc p left join bd_deptdoc d1 on
				// p.pk_deptdoc = d1.pk_deptdoc left join bd_deptdoc d2 on
				// d1.pk_fathedept = d2.pk_deptdoc left join bd_corp c on
				// p.pk_corp = c.pk_corp where p.psncode = ? and p.psnclscope =
				// '0'
				if (rs.next()) {
					reinstatementInfoBean.setNewCompany(rs.getString(1));
					String depID = rs.getString(5);
					if (depID != null && !"".equals(depID)) {
						reinstatementInfoBean.setNewChildDep(rs.getString(2));
						reinstatementInfoBean.setNewDep(rs.getString(4));
					} else {
						reinstatementInfoBean.setNewDep(rs.getString(2));
					}
					// 申请人所在部门
					depID = MyUtil.strIsNotNull(depID) ? depID : rs
							.getString(3);
					DBUtil.close(rs, pst);

					pst = conn.prepareStatement(queryNewPosts);

					pst.setString(1, depID);
					pst.setString(2, depID);

					rs = pst.executeQuery();
					List<JobBean> newPosts = new ArrayList<JobBean>();
					JobBean jb = null;
					while (rs.next()) {
						// select j.jobname, j.pk_jobdoc, def.docname from
						// om_job j left join bd_deptdoc d on j.pk_deptdoc =
						// d.pk_deptdoc left join bd_defdoc def on j.jobrank =
						// def.pk_defdoc where j.dr = '0' and d.pk_deptdoc = ?
						jb = new JobBean();
						jb.setId(rs.getString(2));
						jb.setName(rs.getString(1));
						jb.setRank(rs.getString(3));
						newPosts.add(jb);
						jb = null;
					}
					reinstatementInfoBean.setNewPosts(newPosts);
					success = true;
				} else {
					errorMsg = "无法在HR系统中查询到填单人对应的制造部/部门";
				}
			}
		} catch (Exception e) {
			StringBuilder sb = new StringBuilder().append("查询ＨＲ中的制造部列表失败");
			log.error(sb, e);
			System.out.println(sb);
			e.printStackTrace();

		} finally {
			DBUtil.close(rs);
			DBUtil.close(pst);
			DBUtil.close(conn);
		}

		return success;

	}
}

class ReinstatementInfoBean {

	private String memberCode;

	private String memberName;

	private String lastJoinDate;

	private String lastLeaveDate;

	private String workYear;

	private String educationalBackground;

	private String address;

	private String birthdate;

	private String newCompany;

	private String newDep;

	private String newChildDep;

	private List<JobBean> newPosts;

	private List<OptionBean> companyList;

	private OptionBean oldCompany;

	private OptionBean oldDep;

	private OptionBean oldChildDep;

	private JobBean oldPost;

	private boolean exist;

	private String lvliJSON;

	public String getLvliJSON() {
		return lvliJSON;
	}

	public void setLvliJSON(String lvliJSON) {
		this.lvliJSON = lvliJSON;
	}

	public String getBirthdate() {
		return birthdate;
	}

	public void setBirthdate(String birthdate) {
		this.birthdate = birthdate;
	}

	public String getMemberCode() {
		return memberCode;
	}

	public void setMemberCode(String memberCode) {
		this.memberCode = memberCode;
	}

	public String getMemberName() {
		return memberName;
	}

	public void setMemberName(String memberName) {
		this.memberName = memberName;
	}

	public String getLastJoinDate() {
		return lastJoinDate;
	}

	public void setLastJoinDate(String lastJoinDate) {
		this.lastJoinDate = lastJoinDate;
	}

	public String getLastLeaveDate() {
		return lastLeaveDate;
	}

	public void setLastLeaveDate(String lastLeaveDate) {
		this.lastLeaveDate = lastLeaveDate;
	}

	public String getWorkYear() {
		return workYear;
	}

	public void setWorkYear(String workYear) {
		this.workYear = workYear;
	}

	public String getEducationalBackground() {
		return educationalBackground;
	}

	public void setEducationalBackground(String educationalBackground) {
		this.educationalBackground = educationalBackground;
	}

	public String getAddress() {
		return address;
	}

	public void setAddress(String address) {
		this.address = address;
	}

	public boolean isExist() {
		return exist;
	}

	public void setExist(boolean exist) {
		this.exist = exist;
	}

	public String getNewCompany() {
		return newCompany;
	}

	public void setNewCompany(String newCompany) {
		this.newCompany = newCompany;
	}

	public String getNewDep() {
		return newDep;
	}

	public void setNewDep(String newDep) {
		this.newDep = newDep;
	}

	public String getNewChildDep() {
		return newChildDep;
	}

	public void setNewChildDep(String newChildDep) {
		this.newChildDep = newChildDep;
	}

	public List<JobBean> getNewPosts() {
		return newPosts;
	}

	public void setNewPosts(List<JobBean> newPosts) {
		this.newPosts = newPosts;
	}

	public List<OptionBean> getCompanyList() {
		return companyList;
	}

	public void setCompanyList(List<OptionBean> companyList) {
		this.companyList = companyList;
	}

	public OptionBean getOldCompany() {
		return oldCompany;
	}

	public void setOldCompany(OptionBean oldCompany) {
		this.oldCompany = oldCompany;
	}

	public OptionBean getOldDep() {
		return oldDep;
	}

	public void setOldDep(OptionBean oldDep) {
		this.oldDep = oldDep;
	}

	public OptionBean getOldChildDep() {
		return oldChildDep;
	}

	public void setOldChildDep(OptionBean oldChildDep) {
		this.oldChildDep = oldChildDep;
	}

	public JobBean getOldPost() {
		return oldPost;
	}

	public void setOldPost(JobBean oldPost) {
		this.oldPost = oldPost;
	}
}
